
package com.cy.model;

import com.cy.BasicModel;
import com.cy.ext.plugin.tablebind.TableBind;
import com.google.common.collect.Lists;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.cy.utils.SimpleDateUtils.getMaxTime;
import static com.cy.utils.SimpleDateUtils.getMinTime;
import static com.cy.utils.SimpleSqlUtils.generateSql;
import static com.cy.utils.SimpleSqlUtils.in;
import static org.apache.commons.lang3.StringUtils.isNotBlank;

/**
* Created with IntelliJ IDEA.
* User: Administrator
* Date: 13-9-9
* Time: 下午12:56
* To change this template use File | Settings | File Templates.
*/
@SuppressWarnings("serial")
@TableBind(tableName="brewed_log",pkName = "log_id")
public class BrewedLog extends BasicModel<BrewedLog> {
    public static final BrewedLog dao = new BrewedLog();
    public static final String SELECT_ALL = "`log_id`,`device_id`,`create_time`,`product_id`,`steep_temp`,`steep_volume`,`steep_time`,`brew_volume`,`user_id`,`remark`,`brew_type`,`brew_time`";

    public enum BrewType{
        CLEAR(0),
        AABREW(1),
        DEVICEBREW(2),
        AABREW_TEST(3);

        private int value;
        public int getValue() {
            return value;
        }

        private BrewType(int value) {
            this.value = value;
        }
    }

    public BrewedLog existLog(Long userId,Long device,Date brewTime){
        return findFirst("select log_id from " + getTableName() +" where user_id = ? and device_id = ? and brew_time = ? order by log_id desc ",userId,device,brewTime);
    }

    /**
     * 按年份统计
     *
     * @param from 从from年
     * @param to 到to年
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByYear(String from, String to, String[] deviceIds){
        List<String> selects = Lists.newArrayList("YEAR(brew_time) AS 'date'",
                "COUNT(CASE WHEN brew_type = 2 THEN 0 END) AS 'local'",
                "COUNT(CASE WHEN brew_type = 1 THEN 0 END) AS 'app'");
        List<String> tables = Lists.newArrayList("brewed_log_archived");
        List<String> wheres = new ArrayList<>();
        if (isNotBlank(from))
            wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
        if (isNotBlank(to))
            wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
        if (deviceIds != null && deviceIds.length > 0)
            wheres.add(in("device_id", deviceIds));
        List<String> groupBy = Lists.newArrayList("YEAR(brew_time)");
        List<String> orderBy = Lists.newArrayList("brew_time");
        List<Record> records = Db.find(generateSql(selects, tables, null, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(5, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> appCountList = new ArrayList<>();//APP冲泡统计(次)
        List<Object> localCountList = new ArrayList<>();//本机冲泡统计(次)
        int sumApp = 0;//APP冲泡次数净增总数
        int sumLocal = 0;//本机冲泡次数净增总数
        for (Record record : records){
            dateList.add(record.getInt("date"));
            appCountList.add(record.getLong("app"));
            localCountList.add(record.getLong("local"));
            sumApp += record.getLong("app");
            sumLocal += record.getLong("local");
        }
        result.put("date", dateList);
        result.put("local", localCountList);
        result.put("app", appCountList);
        result.put("sumApp", sumApp);
        result.put("sumLocal", sumLocal);
        return result;
    }

    /**
     * 按季度统计
     *
     * @param from 从yyyy-MM
     * @param to 到yyyy-MM
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByQuarter(String from, String to, String[] deviceIds){
        List<String> selects = Lists.newArrayList("YEAR(brew_time) AS year",
                "QUARTER(brew_time) AS quarter",
                "COUNT(CASE WHEN brew_type = 2 THEN 0 END) AS 'local'",
                "COUNT(CASE WHEN brew_type = 1 THEN 0 END) AS 'app'");
        List<String> tables = Lists.newArrayList("brewed_log_archived");
        List<String> wheres = new ArrayList<>();
        if (isNotBlank(from))
            wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
        if (isNotBlank(to))
            wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
        if (deviceIds != null && deviceIds.length > 0)
            wheres.add(in("device_id", deviceIds));
        List<String> groupBy = Lists.newArrayList("YEAR(brew_time)","QUARTER(brew_time)");
        List<String> orderBy = Lists.newArrayList("brew_time");
        List<Record> records = Db.find(generateSql(selects, tables, null, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(5, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> appCountList = new ArrayList<>();//APP冲泡统计(次)
        List<Object> localCountList = new ArrayList<>();//本机冲泡统计(次)
        int sumApp = 0;//APP冲泡次数净增总数
        int sumLocal = 0;//本机冲泡次数净增总数
        for (Record record : records){
            dateList.add(record.getInt("year") +"-Q"+ record.getInt("quarter"));
            appCountList.add(record.getLong("app"));
            localCountList.add(record.getLong("local"));
            sumApp += record.getLong("app");
            sumLocal += record.getLong("local");
        }
        result.put("date", dateList);
        result.put("local", localCountList);
        result.put("app", appCountList);
        result.put("sumApp", sumApp);
        result.put("sumLocal", sumLocal);
        return result;
    }

    /**
     * 按月份统计
     * @param from 从yyyy-MM
     * @param to 到yyyy-MM
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByMonth(String from, String to, String[] deviceIds){
        List<String> selects = Lists.newArrayList("DATE_FORMAT(brew_time,'%Y-%m') AS 'date'",
                "COUNT(CASE WHEN brew_type = 2 THEN 0 END) AS 'local'",
                "COUNT(CASE WHEN brew_type = 1 THEN 0 END) AS 'app'");
        List<String> tables = Lists.newArrayList("brewed_log_archived");
        List<String> wheres = new ArrayList<>();
        if (isNotBlank(from))
            wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
        if (isNotBlank(to))
            wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
        if (deviceIds != null && deviceIds.length > 0)
            wheres.add(in("device_id", deviceIds));
        List<String> groupBy = Lists.newArrayList("YEAR(brew_time)","MONTH(brew_time)");
        List<String> orderBy = Lists.newArrayList("brew_time");
        List<Record> records = Db.find(generateSql(selects, tables, null, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(5, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> appCountList = new ArrayList<>();//APP冲泡统计(次)
        List<Object> localCountList = new ArrayList<>();//本机冲泡统计(次)
        int sumApp = 0;//APP冲泡次数净增总数
        int sumLocal = 0;//本机冲泡次数净增总数
        for (Record record : records){
            dateList.add(record.getStr("date"));
            appCountList.add(record.getLong("app"));
            localCountList.add(record.getLong("local"));
            sumApp += record.getLong("app");
            sumLocal += record.getLong("local");
        }
        result.put("date", dateList);
        result.put("app", appCountList);
        result.put("local", localCountList);
        result.put("sumApp", sumApp);
        result.put("sumLocal", sumLocal);
        return result;
    }

    /**
     * 按周统计
     *
     * @param from 从yyyy-MM-dd
     * @param to 到yyyy-MM-dd
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByWeek(String from, String to, String[] deviceIds){
        List<String> selects = Lists.newArrayList("YEAR(brew_time) AS year",
                "WEEKOFYEAR(brew_time) AS week",
                "COUNT(CASE WHEN brew_type = 2 THEN 0 END) AS 'local'",
                "COUNT(CASE WHEN brew_type = 1 THEN 0 END) AS 'app'");
        List<String> tables = Lists.newArrayList("brewed_log_archived");
        List<String> wheres = new ArrayList<>();
        if (isNotBlank(from))
            wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
        if (isNotBlank(to))
            wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
        if (deviceIds != null && deviceIds.length > 0)
            wheres.add(in("device_id", deviceIds));
        List<String> groupBy = Lists.newArrayList("YEAR(brew_time)","WEEKOFYEAR(brew_time)");
        List<String> orderBy = Lists.newArrayList("brew_time");
        List<Record> records = Db.find(generateSql(selects, tables, null, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(5, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> appCountList = new ArrayList<>();//APP冲泡统计(次)
        List<Object> localCountList = new ArrayList<>();//本机冲泡统计(次)
        int sumApp = 0;//APP冲泡次数净增总数
        int sumLocal = 0;//本机冲泡次数净增总数
        for (Record record : records){
            dateList.add(record.getInt("year") +"-W"+ record.getInt("week"));
            appCountList.add(record.getLong("app"));
            localCountList.add(record.getLong("local"));
            sumApp += record.getLong("app");
            sumLocal += record.getLong("local");
        }
        result.put("date", dateList);
        result.put("app", appCountList);
        result.put("local", localCountList);
        result.put("sumApp", sumApp);
        result.put("sumLocal", sumLocal);
        return result;
    }

    /**
     * 按天统计
     * @param from 从yyyy-MM-dd
     * @param to 到yyyy-MM-dd
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByDay(String from, String to, String[] deviceIds){
        List<String> selects = Lists.newArrayList("DATE_FORMAT(brew_time,'%Y-%m-%d') AS 'date'",
                "COUNT(CASE WHEN brew_type = 2 THEN 0 END) AS 'local'",
                "COUNT(CASE WHEN brew_type = 1 THEN 0 END) AS 'app'");
        List<String> tables = Lists.newArrayList("brewed_log_archived");
        List<String> wheres = new ArrayList<>();
        if (isNotBlank(from))
            wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
        if (isNotBlank(to))
            wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
        if (deviceIds != null && deviceIds.length > 0)
            wheres.add(in("device_id", deviceIds));
        List<String> groupBy = Lists.newArrayList("DATE(brew_time)");
        List<String> orderBy = Lists.newArrayList("brew_time");
        List<Record> records = Db.find(generateSql(selects, tables, null, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(5, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> appCountList = new ArrayList<>();//APP冲泡统计(次)
        List<Object> localCountList = new ArrayList<>();//本机冲泡统计(次)
        int sumApp = 0;//APP冲泡次数净增总数
        int sumLocal = 0;//本机冲泡次数净增总数
        for (Record record : records){
            dateList.add(record.getStr("date"));
            appCountList.add(record.getLong("app"));
            localCountList.add(record.getLong("local"));
            sumApp += record.getLong("app");
            sumLocal += record.getLong("local");
        }
        result.put("date", dateList);
        result.put("app", appCountList);
        result.put("local", localCountList);
        result.put("sumApp", sumApp);
        result.put("sumLocal", sumLocal);
        return result;
    }

    /**
     * 按小时统计
     * @param from 从yyyy-MM-dd
     * @param to 到yyyy-MM-dd
     * @return Map<String, Object>
     */
    public Map<String,Object> sumByHour(String from, String to, String[] deviceIds) {
        List<String> selects = Lists.newArrayList("HOUR(brew_time) AS 'date'",
                "COUNT(CASE WHEN brew_type = 2 THEN 0 END) AS 'local'",
                "COUNT(CASE WHEN brew_type = 1 THEN 0 END) AS 'app'");
        List<String> tables = Lists.newArrayList("brewed_log_archived");
        List<String> wheres = new ArrayList<>();
        if (isNotBlank(from))
            wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
        if (isNotBlank(to))
            wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
        if (deviceIds != null && deviceIds.length > 0)
            wheres.add(in("device_id", deviceIds));
        List<String> groupBy = Lists.newArrayList("HOUR(brew_time)");
        List<String> orderBy = Lists.newArrayList("date");
        List<Record> records = Db.find(generateSql(selects, tables, null, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(5, 1.0F);
        List<Object> dateList = new ArrayList<>(24);//日期
        List<Object> appCountList = new ArrayList<>(24);//APP冲泡统计(次)
        List<Object> localCountList = new ArrayList<>(24);//本机冲泡统计(次)
        int sumApp = 0;//APP冲泡次数净增总数
        int sumLocal = 0;//本机冲泡次数净增总数
        for (Record record : records){
            dateList.add(record.getInt("date"));
            appCountList.add(record.getLong("app"));
            localCountList.add(record.getLong("local"));
            sumApp += record.getLong("app");
            sumLocal += record.getLong("local");
        }
        result.put("date", dateList);
        result.put("local", localCountList);
        result.put("app", appCountList);
        result.put("sumApp", sumApp);
        result.put("sumLocal", sumLocal);
        return result;
    }

    /**
     * 按年份查询
     *
     * @param from 从from年
     * @param to 到to年
     */
    public List<Record> findByYear(String from, String to, String[] deviceIds){
        List<String> selects = Lists.newArrayList("IFNULL(rmi.province,'其他') AS name", "COUNT(bl.log_id) AS value");
        List<String> tables = Lists.newArrayList("brewed_log_archived bl LEFT JOIN region_map_ip rmi ON bl.app_ip = rmi.ip");
        List<String> wheres = Lists.newArrayList("(bl.brew_type = 1 OR bl.brew_type = 2)");
        List<String> groupBy = Lists.newArrayList("rmi.province");
        if (isNotBlank(from))
            wheres.add("bl.brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
        if (isNotBlank(to))
            wheres.add("bl.brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
        if (deviceIds != null && deviceIds.length > 0)
            wheres.add(in("bl.device_id", deviceIds));
        return Db.find(generateSql(selects, tables, null, wheres, groupBy, null));
    }

    /**
     * 按月份查询
     *
     * @param from 从yyyy-MM
     * @param to 到yyyy-MM
     */
    public List<Record> findByMonth(String from, String to, String[] deviceIds){
        List<String> selects = Lists.newArrayList("IFNULL(rmi.province,'其他') AS name", "COUNT(bl.log_id) AS value");
        List<String> tables = Lists.newArrayList("brewed_log_archived bl LEFT JOIN region_map_ip rmi ON bl.app_ip = rmi.ip");
        List<String> wheres = Lists.newArrayList("(bl.brew_type = 1 OR bl.brew_type = 2)");
        List<String> groupBy = Lists.newArrayList("rmi.province");
        if (isNotBlank(from))
            wheres.add("bl.brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
        if (isNotBlank(to))
            wheres.add("bl.brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
        if (deviceIds != null && deviceIds.length > 0)
            wheres.add(in("bl.device_id", deviceIds));
        return Db.find(generateSql(selects, tables, null, wheres, groupBy, null));
    }

    /**
     * 按天查询
     *
     * @param from 从yyyy-MM-dd
     * @param to 到yyyy-MM-dd
     */
    public List<Record> findByDay(String from, String to, String[] deviceIds){
        List<String> selects = Lists.newArrayList("IFNULL(rmi.province,'其他') AS name", "COUNT(bl.log_id) AS value");
        List<String> tables = Lists.newArrayList("brewed_log_archived bl LEFT JOIN region_map_ip rmi ON bl.app_ip = rmi.ip");
        List<String> wheres = Lists.newArrayList("(bl.brew_type = 1 OR bl.brew_type = 2)");
        List<String> groupBy = Lists.newArrayList("rmi.province");
        if (isNotBlank(from))
            wheres.add("bl.brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
        if (isNotBlank(to))
            wheres.add("bl.brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
        if (deviceIds != null && deviceIds.length > 0)
            wheres.add(in("bl.device_id", deviceIds));
        return Db.find(generateSql(selects, tables, null, wheres, groupBy, null));
    }

    public BrewedLog setLogId(Long logId){
        this.set("log_id",logId);
        return this;
    }

    public Long getLogId(){
        return this. get("log_id");
    }

    public BrewedLog setDeviceId(Long deviceId){
        this.set("device_id",deviceId);
        return this;
    }

    public Long getDeviceId(){
        return this. get("device_id");
    }

    public BrewedLog setCreateTime(Date createTime){
        this.set("create_time",createTime);
        return this;
    }

    public Date getCreateTime(){
        return this.getDate("create_time");
    }

    public BrewedLog setBrewTime(Date brewTime){
        this.set("brew_time",brewTime);
        return this;
    }

    public Date getBrewTime(){
        return this.getDate("brew_time");
    }

    public BrewedLog setProductId(Long productId){
        this.set("product_id",productId);
        return this;
    }

    public Long getProductId(){
        return this. get("product_id");
    }

    public BrewedLog setSteepTemp(Integer steepTemp){
        this.set("steep_temp",steepTemp);
        return this;
    }

    public Integer getSteepTemp(){
        return this. get("steep_temp");
    }

    public BrewedLog setSteepVolume(Integer steepVolume){
        this.set("steep_volume",steepVolume);
        return this;
    }

    public Integer getSteepVolume (){
        return this. get("steep_volume");
    }

    public BrewedLog setSteepTime(Integer steepTime){
        this.set("steep_time",steepTime);
        return this;
    }

    public Integer getSteepTime(){
        return this. get("steep_time");
    }

    public BrewedLog setBlowTime(Integer steepTime){
        this.set("blow_time",steepTime);
        return this;
    }

    public Integer getBlowTime(){
        return this. get("blow_time");
    }

    public BrewedLog setBrewVolume(Integer brewVolume){
        this.set("brew_volume",brewVolume);
        return this;
    }

    public Integer getBrewVolume(){
        return this. get("brew_volume");
    }

    public BrewedLog setUserId(Long userId){
        this.set("user_id",userId);
        return this;
    }

    public Long getUserId(){
        return this. get("user_id");
    }

    public BrewedLog setBrewType (Integer type){
        this.set("brew_type",type);
        return this;
    }

    public Integer getBrewType (){
        return this. get("brew_type");
    }
    public BrewedLog setBrewCategory (Integer category){
        this.set("brew_category",category);
        return this;
    }

    public Integer getBrewCategory(){
        return this. get("brew_category");
    }

    public BrewedLog setRemark(String remark){
        this.set("remark",remark);
        return this;
    }

    public String getRemark(){
        return this.getStr("remark");
    }

    public BrewedLog setAppIp(String appIp){
        this.set("app_ip",appIp);
        return this;
    }

    public String getAppIp (){
        return this.getStr("app_ip");
    }
}